import openpyxl
import cv2
import numpy as np
import matplotlib.pyplot as plt
from math import sqrt
from xml.dom.minidom import parse
# import pytesseract
import time
import os
from openpyxl.styles import Alignment
from pytesseract import image_to_string
tessdata_dir_config = '--tessdata-dir "c:\\Program Files\\Tesseract-OCR\\tessdata"  --psm 6 7 --user-words "C:\\Users\\wangyu\\Desktop\\font2\\chi_sim.user-words" '
from openpyxl.styles import  PatternFill,colors, fills, Font
import re
from cv2 import MORPH_RECT, MORPH_CLOSE, MORPH_OPEN


# tesseract F3.jpg  a1 -l eng --dpi 96 bazaar
# tesseract F3.jpg  a1 -l eng --dpi 96 --user-words "C:\\Users\\14197\\Desktop\\font2\\chi_sim.user-words"
# C:\Users\14197\Desktop\program_donqi\data\00017\orgin_cells\a1.txt
# C:\Users\wangyu\Desktop\program_donqi\data\00017\orgin_cells\a1.txt
# target = pytesseract.image_to_string(image, lang='eng', boxes=False, config='--psm 10 --oem 3 -c tessedit_char_whitelist=0123456789')
pt = "0006"

def get_cell_processed(All_cell_points, cells_path, img_jiaozheng, table_information):
    """
    该函数的作用：根据每个单元格坐标信息在矫正后的图像中剪裁得到单元格并对其进行预处理
    :param All_cell_points: 每个单元格在图像中的真实的坐标信息
    :param cells_path:剪裁并处理过的单元格图像存放位置
    :param img_jiaozheng:矫正后的图像
    :param table_information:通过解析表格结构信息XML文件得到的真实表格框相关的信息
    :return: 返回图像中所有单元格的列表
    """


    # excel_path = "C:\\Users\\wangyu\\Desktop\\program_donqi\\data\\0001\\00011.xlsx"
    excel_path = "C:\\Users\\wangyu\\Desktop\\program_donqi\\data\\" + pt + "\\" + pt + "1.xlsx"
    wb = openpyxl.load_workbook(excel_path)
    ws = wb.worksheets[0]

    # excel_path1 = "C:\\Users\\wangyu\\Desktop\\program_donqi\\data\\0001\\0001.xlsx"
    excel_path1 = "C:\\Users\\wangyu\\Desktop\\program_donqi\\data\\" + pt + "\\" + pt + ".xlsx"
    wb1 = openpyxl.load_workbook(excel_path1)
    ws1 = wb1.worksheets[0]

    # 检查是否有存放单元格的文件夹
    if not os.path.exists(cells_path):
        os.makedirs(cells_path)

    # 检查表格是否可以保存
    try:
        wb.save(excel_path)
    except:
        print("表格已打开，不可以保存！！！！！！！")


    #读取矫正图size
    # 图片垂直尺寸高h 图片水平尺寸宽w
    real_row, real_col = img_jiaozheng.shape[0:2]

    # 转灰度图 二值化
    img_jiaozheng = cv2.cvtColor(img_jiaozheng,cv2.COLOR_RGB2GRAY)
    ret, _ = cv2.threshold(img_jiaozheng, 0, 255, cv2.THRESH_BINARY | cv2.THRESH_OTSU)
    # ret = 185
    _, img_jiaozheng = cv2.threshold(img_jiaozheng, ret+45, 255, cv2.THRESH_BINARY )
    # img_jiaozheng = thresh

    # 去除框线
    img_jiaozheng = remove_wireframe(img_jiaozheng)

    #根据excel表格获得的表格高、宽
    height_table = table_information[0]
    col_table = table_information[1]

    # 记录ocr与正确结果不一致的单元格索引
    fault_cell = []
    # 记录单元格内容识别正确的数量
    ac =0
    ac_ch = 0
    # 记录表格含有汉语字符的单元格数，即表格标题数量
    table_title = 0
    #存放分割后的单元格图片
    img_cells = []
    #记录循环次数，即单元格数量
    i = 0
    # 加快cv2.imwrite在循环中的速度
    img_write = cv2.imwrite
    # 批量剪裁单元格
    print("\n")
    for cell_points in All_cell_points:

        # 分割单元格的左上角点像素坐标
        x_start = int(cell_points[0][0] / height_table * real_row)
        y_start = int(cell_points[0][1] / col_table * real_col)
        # 分割单元格的右下角点像素坐标
        x_end = int(cell_points[1][0] / height_table * real_row)
        y_end = int(cell_points[1][1] / col_table * real_col)
        # 分割单元格的表格索引
        cell_index = cell_points[2]

        # 对矫正后的表格图像进行分割，获取单元格图像
        cell = img_jiaozheng[x_start:x_end, y_start:y_end]

        # 对单元格图像进行预处理操作
        # img_gray = cv2.cvtColor(cell, cv2.COLOR_BGR2GRAY)

        # 增加白色边界
        img_gray = add_border(cell)

        # 用高斯滤波代替均值滤波，更好的保留图像的整体细节
        # img_guss = cv2.GaussianBlur(img_gray, (5, 5), 0)
        # img_gray = cv2.blur(img_gray, (5, 5))

        # 图像锐化
        # kernel = np.array([[0, -1, 0], [-1, 5, -1], [0, -1, 0]], np.float32)  # 锐化
        # img_sharp = cv2.filter2D(img_guss, -1, kernel=kernel)

        # 这里的二值化阈值需要实现自适应
        # ret, binary_img = cv2.threshold(img_sharp, 100, 255, cv2.THRESH_OTSU)
        # binary_img = ~binary_img
        # img_dilate = cv2.dilate(binary_img, (5, 5), iterations=1)

        # 图像内缩1/10，去除边界的框线
        # h,w = img_gray.shape[:2]
        # img_gray = img_gray[int(h / 20):-int(h / 20), int(w / 20):-int(w / 20)]
        # img_gray = img_gray[int(h / 20):-int(h / 20), int(w / 40):-int(w / 40)]
        # img_gray = img_gray[2:-2, 2:-2]

        # 保存分割获得的单元格图像
        img_write(cells_path + cell_index + ".jpg", img_gray,  [int( cv2.IMWRITE_JPEG_QUALITY), 100] )
        # img_gray.save(cells_path + cell_index + ".jpg" , dpi=(300.0, 300.0))
        img_cells.append(cell)

        lang = 'simsun2'

        # # 对单元格图像进行ocr识别
        ocr = image_to_string(img_gray, lang=lang, config=tessdata_dir_config)
        # 处理中文中间多余的空格
        # ocr = ocr.replace("\n", " ")
        # ocr = ocr.replace(":", "：")
        ocr = clean_space(ocr)
        # from paddleocr import PaddleOCR
        # pdocr = PaddleOCR(rec_model_dir=r'C:/Users/wangyu/Desktop/inference/ch_ppocr_server_v2.0_rec_infer/')  # need to run only once to download and load model into memory
        # result = pdocr.ocr(img_gray, cls=True)

        # import paddlehub as hub
        # phocr = hub.Module(name="chinese_ocr_db_crnn_server")
        # # ocr = hub.Module(name="chinese_ocr_db_crnn_mobile")
        # img_gray = cv2.cvtColor(img_gray, cv2.COLOR_GRAY2BGR)
        # result = phocr.recognize_text(images=[img_gray], box_thresh=0.1, text_thresh=0.1)

        # ocr = ''
        # for line in result:
        #     ocr += line[1][0] + '\n'
        #     # print(line)
        # ocr +='\f'


        # 设置有换行符时单元格换行、文字居中
        ws[cell_index].alignment = Alignment(wrapText=True,horizontal='center', vertical='center')
        # 将识别后的结果写入表格，填充时去除末尾的换行符"\n" 与换页符“\f”
        ws[cell_index] = ocr[:-2]



        is_ch = is_chinese(str(ws1[cell_index].value))
        # 判断是否为中文字符
        if is_ch:
            table_title+=1

        if ws1[cell_index].value ==None:
            ws1[cell_index].value = ''

        fill = PatternFill("solid", fgColor=colors.WHITE)
        ws[cell_index].fill = fill

        text3 = str(ws1[cell_index].value)
        text1 = ws[cell_index].value.replace("\n", " ").replace(" ", "")
        text2 = str(ws1[cell_index].value).replace("\n", " ").replace(" ", "")
        # 判断识别结果是否正确
        if text1 == text2:
        # if ws[cell_index].value == ws1[cell_index].value:
            ac+=1
            if is_ch:
                ac_ch+=1
        else:
            fault_cell.append(cell_index)
            fill = PatternFill("solid", fgColor="1874CD")
            # fill = PatternFill(patternType=fill_type,fgColor=colors.RED,)
            ws[cell_index].fill = fill
            # print(cell_index, text2, text1)
            print(text3)

    # 表格中不包含汉语字符的单元格数量，即内容单元格数量
    table_content = len(All_cell_points)-table_title
    # 总正确率
    accuracy = ac/len(All_cell_points)
    accuracy_ch = ac_ch/table_title
    accuracy_en = (ac-ac_ch) / (len(All_cell_points)-table_title)
    print("\n")
    print("index","accuracy", "ocr")

    print("accuracy", accuracy)
    print("table_title", accuracy_ch,table_title)
    print("table_content", accuracy_en,(len(All_cell_points)-table_title))
    # print(fault_cell)
    print("单元格个数", len(All_cell_points))
    print(lang ,ret,pt)

    # print(ws1['D4'].value, ws['D4'].value)
    # print(ws1['B5'].value, ws['B5'].value)
    wb.save(excel_path)

    return img_cells

def is_chinese(string):
    """
    检查整个字符串是否包含中文
    :param string: 需要检查的字符串
    :return: bool
    """
    for ch in string:
        if u'\u4e00' <= ch <= u'\u9fff':
            return True

    return False


def clean_space(text):
    """"
    处理多余的空格
    """
    match_regex = re.compile(u'[\u4e00-\u9fa5。\.,，:：《》、\(\)（）]{1} +(?<![a-zA-Z])|\d+ +| +\d+|[a-z A-Z]+')
    should_replace_list = match_regex.findall(text)
    order_replace_list = sorted(should_replace_list,key=lambda i:len(i),reverse=True)
    for i in order_replace_list:
        if i == u' ':
            continue
        new_i = i.strip()
        text = text.replace(i,new_i)
    return text

def add_border(img):
    """增加白色边界"""
    h, w = img.shape[0:2]
    img = cv2.copyMakeBorder(img, int(0.1 * h), int(0.1 * h), int(0.1 * w), int(0.1 * w), cv2.BORDER_CONSTANT,value = [255, 255, 255])
    return img

def remove_wireframe(grayImg):
    """检测和删除垂直和水平框线"""

    # 一、morphologyEx：调节形态学算子，检测水平竖直线
    # 开运算保留X方向直线
    kernel = cv2.getStructuringElement(MORPH_RECT, ksize=(1, 100))
    grayImgy = cv2.morphologyEx(grayImg, MORPH_CLOSE, kernel)
    # TODO 100应根据行高列宽二倍均值取值
    # 开运算保留Y方向直线
    kernel = cv2.getStructuringElement(MORPH_RECT, ksize=(100, 1))
    grayImgx = cv2.morphologyEx(grayImg, MORPH_CLOSE, kernel)
    # 获取框线图像
    addImg = cv2.add(~grayImgy, ~grayImgx)

    # 二、将框线图二值化并进行膨胀获取掩膜
    # 将框线图二值化
    ret, thresh = cv2.threshold(addImg, 125, 255, cv2.THRESH_BINARY)
    # 膨胀图像
    kernel = cv2.getStructuringElement(MORPH_RECT, ksize=(3, 3))
    dilated = cv2.dilate(thresh, kernel)

    # 三、原图与框线图相加从而去除框线
    resImg = cv2.add(grayImg, dilated)

    return resImg




def process_lines(img):
    """
    该函数的作用：去除单元格
    :param img: 单元格图象
    :return: 去除单元格框线的单元格图象
    """
    img_cell = cv2.cvtColor(~img, cv2.COLOR_BGR2GRAY)
    h, w = img_cell.shape
    minLineLength = 0
    maxLineGap = 0
    lines = cv2.HoughLinesP(img_cell, 1, np.pi / 180, 1, minLineLength, maxLineGap)
    for line in lines:
        x1 = line[0][0]
        y1 = line[0][1]
        x2 = line[0][2]
        y2 = line[0][3]
        # print(sqrt((x2 - x1)**2 + (y2 - y1)**2))
        if sqrt((x2 - x1) ** 2 + (y2 - y1) ** 2) < 0:
            break
        x_mid = (x1 + x2) / 2
        y_mid = (y1 + y2) / 2
        # 对单元格边缘框线进行筛选
        if x_mid < 0.01 * w or x_mid > 0.9 * w or y_mid < 0.1 * h or y_mid > 0.9 * h:
            cv2.line(img_cell, (x1, y1), (x2, y2), (0, 0, 0), 2)

    return img_cell
